
' ------------------------------------------------
' Code prior to Hands-On 9-1
' ------------------------------------------------

Sub DeleteSheets()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If InStr(ws.Name, "wages") Then
            ws.Delete
        End If
    Next
End Sub


'--------------------------------------------------
' Hands-On 9-1
'--------------------------------------------------

Sub GetComments()
    Dim sht As Worksheet
    Dim colNotes As New Collection
    Dim myNote As Comment
    Dim I As Integer
    Dim t As Integer
    Dim strName As String

    strName = InputBox("Enter author's name:")
    For Each sht In ThisWorkbook.Worksheets
        sht.Select
        I = ActiveSheet.Comments.Count
        For Each myNote In ActiveSheet.Comments
            If myNote.Author = strName Then
                MsgBox myNote.Text
                If colNotes.Count = 0 Then
                    colNotes.Add Item:=myNote, Key:="first"
                Else
                    colNotes.Add Item:=myNote, Before:=1
                End If
            End If
        Next
        t = t + I
    Next
    If colNotes.Count <> 0 Then MsgBox colNotes("first").Text
    MsgBox "Total comments in workbook: " & t & Chr(13) & _
        "Total comments in collection:" & colNotes.Count
    Debug.Print "Comments by " & strName
    For Each myNote In colNotes
        Debug.Print Mid(myNote.Text, Len(myNote.Author) + 2, _
            Len(myNote.Text))
    Next
End Sub


'--------------------------------------------------
' Hands-On 9-2
'--------------------------------------------------

Sub GetComments2()
    Dim sht As Worksheet
    Dim colNotes As New Collection
    Dim myNote As Comment
    Dim I As Integer
    Dim t As Integer
    Dim strName As String
    Dim response
    Dim myID As Integer

    strName = InputBox("Enter author's name:")
    For Each sht In ThisWorkbook.Worksheets
        sht.Select
        I = ActiveSheet.Comments.Count
            For Each myNote In ActiveSheet.Comments
                If myNote.Author = strName Then
                    MsgBox myNote.Text
                    If colNotes.Count = 0 Then
                        colNotes.Add Item:=myNote, Key:="first"
                    Else
                        colNotes.Add Item:=myNote, Before:=1
                    End If
                End If
            Next
        t = t + I
    Next
    If colNotes.Count <> 0 Then MsgBox colNotes("first").Text
    MsgBox "Total comments in workbook: " & t & Chr(13) & _
        "Total comments in collection:" & colNotes.Count
    Debug.Print "Comments by " & strName
        myID = 1
        For Each myNote In colNotes
            Debug.Print Mid(myNote.Text, Len(myNote.Author) + 2, _
                Len(myNote.Text))
            response = MsgBox("Remove this comment?" & Chr(13) _
                & Chr(13) & myNote.Text, vbYesNo + vbQuestion)
            If response = 6 Then
                colNotes.Remove Index:=myID
            Else
                myID = myID + 1
            End If
        Next
    MsgBox "Total notes in workbook: " & t & Chr(13) & _
        "Total notes in collection:" & colNotes.Count
    Debug.Print "The following comments remain in the collection:"
        For Each myNote In colNotes
            Debug.Print Mid(myNote.Text, Len(myNote.Author) + 2, _
                Len(myNote.Text))
        Next
End Sub


'--------------------------------------------------
' Custom Project 9-1a
' No code in this Custom Project.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Custom Project 9-1b
'--------------------------------------------------

' declarations
Private m_LastName As String
Private m_FirstName As String
Private m_Salary As Currency
Private m_ID As String


'--------------------------------------------------
' Custom Project 9-1c
'--------------------------------------------------

Property Get ID() As String
    ID = m_ID
End Property

Property Get LastName() As String
    LastName = m_LastName
End Property

Property Get FirstName() As String
    FirstName = m_FirstName
End Property

Property Get Salary() As Currency
    Salary = m_Salary
End Property


'--------------------------------------------------
' Custom Project 9-1d
'--------------------------------------------------

Property Let LastName(L As String)
	m_LastName = L
End Property

Property Let FirstName(F As String)
	m_FirstName = F
End Property

Property Let Salary(ByVal dollar As Currency)
	m_Salary = dollar
End Property


'--------------------------------------------------
' Custom Project 9-1e
'--------------------------------------------------

Public Function CalcNewSalary(choice As Integer, _
        curSalary As Currency, amount As Long) As Currency
    Select Case choice
        Case 1 ' by percent
            CalcNewSalary = curSalary + ((curSalary + amount) / 100)
        Case 2 ' by amount
            CalcNewSalary = curSalary + amount
    End Select
End Function


'--------------------------------------------------
' Custom Project 9-1f
'--------------------------------------------------

Private Sub Class_Initialize()
    Randomize
    m_ID = Int((99999 - 10000) * Rnd + 10000)
End Sub


'--------------------------------------------------
' Custom Project 9-1g
' No code in this Custom Project.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Custom Project 9-1h
' No code in this Custom Project.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Custom Project 9-1i
'--------------------------------------------------

Dim emp As New CEmployee
Dim CEmployees As New Collection
Dim index As Integer
Dim ws As Worksheet
Dim extract As String
Dim cell As Range
Dim lastRow As Integer
Dim empLoc As Integer
Dim startRow As Integer
Dim endRow As Integer
Dim choice As Integer
Dim amount As Long


Private Sub UserForm_Initialize()
    txtLastName.SetFocus
    cmdEmployeeList.Visible = False
    lboxPeople.Enabled = False
    Frame1.Enabled = False
    txtRaise.Value = ""
    optPercent.Value = False
    optAmount.Value = False
    txtRaise.Enabled = False
    optPercent.Enabled = False
    optAmount.Enabled = False
    Frame2.Enabled = False
    optHighlighted.Enabled = False
    optAll.Enabled = False
    cmdUpdate.Enabled = False
    cmdDelete.Enabled = False
End Sub


Private Sub cmdSave_Click()
    If txtLastName.Value = "" Or txtFirstName.Value = "" Or _  	txtSalary.Value = "" Then
        MsgBox "Enter Last Name, First Name and Salary."
        txtLastName.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(txtSalary) Then
        MsgBox "You must enter a value for the Salary."
    txtSalary.SetFocus
        Exit Sub
    End If

    If txtSalary < 0 Then
        MsgBox "Salary cannot be a negative number."
        Exit Sub
    End If

    Worksheets("Salaries").Select
    index = ActiveSheet.UsedRange.Rows.Count + 1
    lboxPeople.Enabled = True

    'set and enter data into the CEmployees collection
    With emp
        Cells(index, 1).Formula = emp.ID
        .LastName = txtLastName
        Cells(index, 2).Formula = emp.LastName
        .FirstName = txtFirstName
        Cells(index, 3).Formula = emp.FirstName
        .Salary = CCur(txtSalary)
        If .Salary = 0 Then Exit Sub
        Cells(index, 4).Formula = emp.Salary
        CEmployees.Add emp
    End With

    'delete data from text boxes
    txtLastName = ""
    txtFirstName = ""
    txtSalary = ""
    
    'enable hidden controls
    cmdEmployeeList.Value = True
    cmdUpdate.Enabled = True
    cmdDelete.Enabled = True
    Frame1.Enabled = True
    txtRaise.Enabled = True
    optPercent.Enabled = True
    optAmount.Enabled = True
    Frame2.Enabled = True
    optHighlighted.Enabled = True
    optAll.Enabled = True
    txtLastName.SetFocus
End Sub


Private Sub cmdEmployeeList_Click()
    lboxPeople.Clear
    For Each emp In CEmployees
    lboxPeople.AddItem emp.ID & ", " & _
        emp.LastName & ", " & emp.FirstName & ", $" & _
        Format(emp.Salary, "0.00")
    Next emp
End Sub


Private Sub cmdClose_Click()
    Unload Me
End Sub


Private Sub cmdDelete_Click()
' make sure an employee is highlighted in the
' list box control
 
    If lboxPeople.ListIndex > -1 Then
        MsgBox "Selected item number: " & lboxPeople.ListIndex
        extract = CEmployees.Item(lboxPeople.ListIndex + 1).ID
        MsgBox extract
        
        Call FindId
        MsgBox empLoc
        Range("A" & empLoc).Delete (3)
        
        MsgBox "There are " & CEmployees.Count & _
            " items in the CEmployees collection. "
        CEmployees.Remove lboxPeople.ListIndex + 1
        MsgBox "The CEmployees collection has now " & CEmployees.Count & " items."
        cmdEmployeeList.Value = True
            If CEmployees.Count = 0 Then
                Call UserForm_Initialize
            End If
    Else
         MsgBox "Click the item you want to remove."
    End If
End Sub



Private Function FindId()
    Set ws = ActiveWorkbook.Sheets("Salaries")
    startRow = ActiveSheet.UsedRange.Rows.Count + 1 - CEmployees.Count
    endRow = ActiveSheet.UsedRange.Rows.Count
    For Each cell In ws.Range(Cells(startRow, 1), Cells(endRow, 1))
        If cell.Value = extract Then
          empLoc = cell.Row
          FindId = empLoc
            Exit Function
        End If
    Next
End Function


Private Sub cmdUpdate_Click()
    If optHighlighted = False And optAll = False Then
        MsgBox "Click the 'Highlighted Employee' or " _
            & " 'All Employees' option button."
        Exit Sub
    End If
    If Not IsNumeric(txtRaise) Then
        MsgBox "This field requires a number."
        txtRaise.SetFocus
        Exit Sub
    End If
    If optHighlighted = True And _
        lboxPeople.ListIndex = -1 Then
            MsgBox "Click the name of the employee."
            Exit Sub
    End If
    If lboxPeople.ListIndex <> -1 And _
        optHighlighted = True And _
        optAmount.Value = True And _
        txtRaise.Value <> "" Then
            extract = CEmployees.Item(lboxPeople.ListIndex + 1).ID
            MsgBox extract
            Call FindId
            MsgBox empLoc
            choice = 2
            amount = txtRaise
            CEmployees.Item(lboxPeople.ListIndex + 1).Salary = _
                emp.CalcNewSalary(choice, _
            CEmployees.Item(lboxPeople.ListIndex + 1).Salary, amount)
            Range("D" & empLoc).Formula = CEmployees. _
                Item(lboxPeople.ListIndex + 1).Salary
            cmdEmployeeList.Value = True
    ElseIf lboxPeople.ListIndex <> -1 And _
            optHighlighted = True And _
            optPercent.Value = True And _
            txtRaise.Value <> "" Then
                extract = CEmployees.Item(lboxPeople.ListIndex + 1).ID
                MsgBox extract
                Call FindId
                MsgBox empLoc
                CEmployees.Item(lboxPeople.ListIndex + 1).Salary = _
                    CEmployees.Item(lboxPeople.ListIndex + 1).Salary + _
                    (CEmployees.Item(lboxPeople.ListIndex + 1).Salary * _
                    txtRaise / 100)
                Range("D" & empLoc).Formula = CEmployees. _
                    Item(lboxPeople.ListIndex + 1).Salary
                cmdEmployeeList.Value = True
    ElseIf optAll = True And _
            optPercent.Value = True And _
            txtRaise.Value <> "" Then
                For Each emp In CEmployees
                    emp.Salary = emp.Salary + ((emp.Salary * txtRaise) / 100)
                    extract = emp.ID
                    MsgBox extract
                    Call FindId
                    MsgBox empLoc
                    Range("D" & empLoc).Formula = emp.Salary
                Next emp
                cmdEmployeeList.Value = True
    ElseIf optAll = True And _
            optAmount.Value = True And _
            txtRaise.Value <> "" Then
                For Each emp In CEmployees
                    emp.Salary = emp.Salary + txtRaise
                    extract = emp.ID
                    MsgBox extract
                    Call FindId
                    MsgBox empLoc
                    Range("D" & empLoc).Formula = emp.Salary
                Next emp
                cmdEmployeeList.Value = True
    Else
        MsgBox "Enter data or select an option."
    End If
End Sub



'--------------------------------------------------
' Custom Project 9-1j
'--------------------------------------------------

Sub ClassDemo()
    Salaries.Show
End Sub


'--------------------------------------------------
' Custom Project 9-1k
' No code in this Custom Project.
' Please follow the instructions in the book.
'--------------------------------------------------

